from libraryms import app
from flask_cors import cross_origin
from flask import render_template, jsonify, request,send_file,Response
import os
import json  # 新增此行
from flask_jwt_extended import create_access_token
from libraryms import db
from libraryms.models import normalusr,adminusr,usrinfo,usridea,bookitem,bookCollect,bookBorrow,bookBorrowHistory,booknewitem,bookDefaultRecord,messageboardparentcomment,messageboardchildcomment,Playlist,UserCollectedPlaylist
from dashscope import Application
import dashscope
import random
from sqlalchemy import desc  # 必须添加这行导入
from http import HTTPStatus
from sqlalchemy.exc import SQLAlchemyError

@app.route('/')
@app.route('/login')
@app.route('/register')
@app.route('/bookpreview')
@app.route('/permissionerror')
@app.route('/home')
# @app.route('/home/bookList')
@app.route('/home/Playlist')
@app.route('/home/HotPlaylistRanking')
@app.route('/home/user-collections')
@app.route('/home/all-collections')
@app.route('/home/messageBoard')
@app.route('/home/borrowList')
@app.route('/home/borrowHistory')
@app.route('/home/recommend')
@app.route('/home/defaultRecord')
@app.route('/home/collectList')
@app.route('/home/self')
@app.route('/home/userProfile')
@app.route('/home/DeepSeekAssistant')
@app.route('/home/infoCheck')
@app.route('/home/readerManage')
@cross_origin()
def index():
    return render_template('index.html')

@app.errorhandler(404)
def indexerror(error):
    return render_template('index.html'),404

@app.route('/images/<filename>')
def get_image(filename):
    # 获取当前文件所在的目录
    current_dir = os.path.dirname(os.path.abspath(__file__))
    # 拼接图片文件路径
    file_path = os.path.join(current_dir, '..','pics', filename)
    # 使用 Flask 的 send_file 函数来返回图片
    return send_file(file_path, mimetype='image/jpeg')

# 登录、注册------------------------------------------------
@app.route("/login", methods=["POST"])
@cross_origin()
def login():
    sth = request.json
    # print(sth)
    username = sth['username']
    password = sth['password']
    # 非管理员会少传一个数据项，所以需要额外判断
    if 'isAdmin' in sth.keys():
        isadmin = sth['isAdmin']
    else:
        isadmin=''

    # 判断是否为管理员
    if isadmin:
        # 先查找用户名是否存在
        res1 = adminusr.query.filter(adminusr.username==username).first()
        if not res1:
            return jsonify({"msg": "用户名错误"}), 401
        else:
        # 找到用户名，再比对密码是否正确
            res1pwd = res1.password
            if res1pwd != password:
                return jsonify({"msg": "密码错误"}), 401
    else:
        # 先查找用户名是否存在
        res1 = normalusr.query.filter(normalusr.username == username).first()
        if not res1:
            return jsonify({"msg": "用户名错误"}), 401
        else:
            # 找到用户名，再比对密码是否正确
            res1pwd = res1.password
            if res1pwd != password:
                return jsonify({"msg": "密码错误"}), 401

    # 用户名和密码正确，返回token
    access_token = create_access_token(identity=username)
    return jsonify(access_token=access_token)

@app.route("/register", methods=["POST"])
@cross_origin()
def register():
    sth = request.json
    # print(sth)
    username = sth['nickname']
    password = sth['password']
    gender = sth['gender']
    phone = sth['phone']
#     判断用户名是否重合，普通用户能注册，所以检查普通用户表
    res1 = normalusr.query.filter(normalusr.username == username).first()
    if res1:
        return jsonify({"msg": "用户名重复！"}), 401
    else:
        # 普通表写入信息
        m1 = normalusr(username=username, password=password)
        db.session.add(m1)
        db.session.commit()
        # 信息表填入信息
        m2 = usrinfo(username=username,tel=phone,sex=gender)
        db.session.add(m2)
        db.session.commit()
        return jsonify({"msg": "注册成功！","ok":"true"}), 200
# --------------------------------------------------

# 个人信息页路由------------------------------------------------
@app.route('/selfdata', methods=["GET"])
@cross_origin()
def self():
    # print(request)
    sth = request.args
    # print(sth)
    username = sth['username']
    res1 = usrinfo.query.filter(usrinfo.username == username).first()
    response = {"username":username,"tel":res1.tel,"sex":res1.sex,"intro":res1.intro}
    # print(response)
    return jsonify(response)

@app.route('/selfchange', methods=["POST"])
@cross_origin()
def selfchange():
    sth = request.json
    # print('selfchange:',sth)
    username = sth['username']
    # 根据名字，查找到对应的表项
    res1 = usrinfo.query.filter(usrinfo.username == username).first()
    res1.intro = sth['intro']
    res1.tel = sth['tel']
    db.session.commit()
    # print(res1)
    return jsonify({"username":res1.username,"tel":res1.tel,"sex":res1.sex,"intro":res1.intro})

@app.route('/pwdchange', methods=["POST"])
@cross_origin()
def pwdchange():
    sth = request.json
    # print('selfmsg:', sth)
    username = sth['username']
    isadmin = sth['isadmin']
    if isadmin=='true':
        # 根据名字，查找到对应的表项
        # print(1)
        res1 = adminusr.query.filter(adminusr.username == username).first()
        res1.password = sth['password']
    else:
        # print(2)
        res1 = normalusr.query.filter(normalusr.username == username).first()
        res1.password = sth['password']
    db.session.commit()
    return {"msg": "ok！"}

@app.route('/selfidea', methods=["GET"])
@cross_origin()
def selfidea():
    sth = request.args
    # print(sth)
    username = sth['username']
    res1 = usridea.query.filter(usridea.username == username).all()
    # print(res1)
    response = []
    for x in res1:
        item = {'username':x.username,"text":x.text}
        response.append(item)
    # print(response)
    return response

@app.route('/addidea', methods=["POST"])
@cross_origin()
def addidea():
    sth = request.json
    # print('msg:', sth)
    username = sth['username']
    text = sth['text']
    nitem = usridea(username=username, text=text)
    db.session.add(nitem)
    db.session.commit()
    return jsonify({"msg": "ok！"})

@app.route('/delideas', methods=["POST"])
@cross_origin()
def delideas():
    sth = request.json
    # print('msg:', sth)
    username = sth['username']
    res1 = usridea.query.filter(usridea.username == username).all()
    for x in res1:
        db.session.delete(x)
    db.session.commit()
    return jsonify({"msg": "ok！"})

# 用户画像
@app.route('/userprofile', methods=["GET"])
@cross_origin()
def userprofile():
    sth = request.args
    username = sth['username']
    tagarray = []
    bookarray = []
    # 统计借阅历史
    res1 = bookBorrowHistory.query.filter(bookBorrowHistory.borrowusr == username).all()
    for x in res1:
        bookarray.append(x.name)
    # 统计收藏信息
    res2 = bookCollect.query.filter(bookCollect.username == username).all()
    for x in res2:
        res3 = bookitem.query.filter(bookitem.isbn == x.isbn).first()
        bookarray.append(res3.name)
    # 统计所有标签
    for x in bookarray:
        res4 = bookitem.query.filter(bookitem.name == x).first()
        tagarray.append(res4.type)
    # print(bookarray)
    # print(tagarray)
    # 计算出数量最多的3个标签
    dict = {}
    tag = []
    for x in tagarray:
        dict[x] = dict.get(x,0)+1
    # print(dict)
    if len(dict)>3:
        while (len(tag)<3):
            tag.append(max(dict,key=dict.get))
            dict.pop(max(dict,key=dict.get))
    else:
        for key in dict:
            tag.append(key)
    # print(tag)
    return tag

@app.route('/userrecommend', methods=["POST"])
@cross_origin()
def userrecommend():
    sth = request.json
    tagarray = sth['tagarray']
    book = []
    # print(tagarray)
    for x in tagarray:
        res1 = bookitem.query.filter(bookitem.type == x).all()
        a = random.choice(res1)
        item = {"key": a.bid, "name": a.name, "author": a.author, "publish": a.publish, "isbn": a.isbn,"price": a.price, "number": a.number, "type": a.type, "intro": a.intro, "pubdate": a.pubdate}
        book.append(item)
    # print(book)
    return book

@app.route('/userprofilealldata', methods=["GET"])
@cross_origin()
def userprofilealldata():
    sth = request.args
    username = sth['username']
    tagarray = []
    bookarray = []
    # 统计借阅历史
    res1 = bookBorrowHistory.query.filter(bookBorrowHistory.borrowusr == username).all()
    for x in res1:
        bookarray.append(x.name)
    # 统计收藏信息
    res2 = bookCollect.query.filter(bookCollect.username == username).all()
    for x in res2:
        res3 = bookitem.query.filter(bookitem.isbn == x.isbn).first()
        bookarray.append(res3.name)
    # 统计所有标签
    for x in bookarray:
        res4 = bookitem.query.filter(bookitem.name == x).first()
        tagarray.append(res4.type)

    dict1 = {}
    result = []
    for x in tagarray:
        dict1[x] = dict1.get(x, 0) + 1

    # 取值唯一的数组
    uniquetagarray = list(set(tagarray))
    # 拆分字典，每个取值为一个字典并保存在数组中
    for x in uniquetagarray:
        subdict = dict([(key,dict1[key]) for key in [x]])
        # print(list(subdict.keys()))
        fridict = dict(type=list(subdict.keys())[0],value=list(subdict.values())[0])
        result.append(fridict)
    # print(result)
    return result

# ------------------------------------------------

# 图书类路由-----------------------------------------

# @app.route('/recommendations', methods=["GET"])
# @cross_origin()
# def get_recommendations():
#     try:
#         username = request.args.get('username')
#         if not username:
#             return jsonify({"success": False, "error": "需要用户登录"}), 401
#
#         # 获取用户收藏歌单名称
#         collected_playlists = UserCollectedPlaylist.query.filter_by(username=username).all()
#         collected_names = [p.playlist_name for p in collected_playlists]
#
#         # 获取所有歌单（排除已收藏的）
#         all_playlists = Playlist.query.filter(Playlist.name.notin_(collected_names)).all()
#         if not all_playlists:
#             return jsonify({"success": True, "data": []})
#
#         # --- 核心优化点：用标签交集计数替代TF-IDF ---
#         # 获取用户收藏歌单的所有标签（去重）
#         user_tags = set()
#         for name in collected_names:
#             playlist = Playlist.query.filter_by(name=name).first()
#             if playlist and playlist.tags:
#                 user_tags.update(playlist.tags.replace('，', ',').split(','))
#
#         # 为每个候选歌单计算标签匹配度
#         recommendations = []
#         for p in all_playlists:
#             if not p.tags:
#                 continue
#             tags = set(p.tags.replace('，', ',').split(','))
#             score = len(user_tags & tags)  # 标签交集数量作为评分
#             if score > 0:
#                 recommendations.append((score, p))
#
#         # 按匹配度降序排序，取前20
#         recommendations.sort(key=lambda x: (-x[0], x[1].play_count), reverse=True)
#         result = [p.to_dict() for (score, p) in recommendations[:20]]
#
#         # 冷启动处理：如果无推荐，返回热门歌单
#         if not result:
#             hot = sorted(all_playlists, key=lambda x: x.play_count, reverse=True)[:20]
#             result = [p.to_dict() for p in hot]
#
#         return jsonify({"success": True, "data": result})
#
#     except Exception as e:
#         return jsonify({"success": False, "error": str(e)}), 500

from flask import request, jsonify
from collections import defaultdict
import numpy as np

# 全局存储物品相似度（建议在服务启动时计算一次）
item_similarity = defaultdict(dict)

def calculate_similarity():
    """计算物品相似度矩阵（服务启动时调用一次即可）"""
    # 获取所有收藏记录
    all_collections = UserCollectedPlaylist.query.all()

    # 构建用户-物品矩阵
    user_item_matrix = defaultdict(set)
    for record in all_collections:
        user_item_matrix[record.username].add(record.playlist_name)

    # 计算共现矩阵
    cooccur_matrix = defaultdict(lambda: defaultdict(int))
    for items in user_item_matrix.values():
        items_list = list(items)
        for i in range(len(items_list)):
            for j in range(i + 1, len(items_list)):
                item1, item2 = items_list[i], items_list[j]
                cooccur_matrix[item1][item2] += 1
                cooccur_matrix[item2][item1] += 1

    # 惩罚高频项的改进Jaccard
    for item1 in cooccur_matrix:
        item1_count = UserCollectedPlaylist.query.filter_by(playlist_name=item1).count()
        for item2 in cooccur_matrix[item1]:
            item2_count = UserCollectedPlaylist.query.filter_by(playlist_name=item2).count()
            # 改进公式：加入对数惩罚项
            cooccur = cooccur_matrix[item1][item2]
            union = item1_count + item2_count - cooccur
            similarity = cooccur / (union + 1e-8) * np.log1p(cooccur)  # log(1+cooccur)避免高频项主导
            item_similarity[item1][item2] = similarity
    # # 添加调试日志
    # print(f"共处理 {len(cooccur_matrix)} 个歌单的相似度")
    # sample_item = list(cooccur_matrix.keys())[0] if cooccur_matrix else None
    # print(f"示例歌单 '{sample_item}' 的相似项: {cooccur_matrix.get(sample_item, {})}")
    # print("==== 相似度计算完成 ====")

@app.route('/recommendations', methods=["GET"])
@cross_origin()
def get_recommendations():
    """协同过滤推荐主逻辑"""
    try:
        username = request.args.get('username')
        if not username:
            return jsonify({"success": False, "error": "需要用户登录"}), 401

        # 获取用户收藏歌单
        collected_records = UserCollectedPlaylist.query.filter_by(username=username).all()
        collected_names = {r.playlist_name for r in collected_records}

        # 冷启动处理
        if not collected_names:
            hot_playlists = Playlist.query.order_by(Playlist.play_count.desc()).limit(20).all()
            return jsonify({"success": True, "data": [p.to_dict() for p in hot_playlists]})
        # # 没有强行过滤掉已收藏项
        # # 生成推荐
        # recommendations = defaultdict(float)
        # # 修改推荐聚合部分
        # for item in collected_names:
        #     for similar_item, score in item_similarity.get(item, {}).items():
        #         # 允许已收藏歌单进入推荐列表但降低权重
        #         weight = 0.2 if similar_item in collected_names else 1.0
        #         recommendations[similar_item] += score * weight

        # 修改推荐聚合逻辑
        # # 没有强行过滤掉已收藏项
        recommendations = defaultdict(float)
        for item in collected_names:
            for similar_item, score in item_similarity.get(item, {}).items():
                # 允许推荐已收藏歌单的同系列内容（如不同年份版本）
                if similar_item.startswith(tuple(collected_names)):
                    recommendations[similar_item] += score * 0.5  # 降低权重
                elif similar_item not in collected_names:
                    recommendations[similar_item] += score

        # 在推荐生成阶段强制排除已收藏项
        # 修改推荐聚合代码
        # recommendations = defaultdict(float)
        # for item in collected_names:
        #     for similar_item, score in item_similarity.get(item, {}).items():
        #         # 严格过滤已收藏项
        #         if similar_item not in collected_names:
        #             recommendations[similar_item] += score

        # 排序并获取前20
        # sorted_items = sorted(recommendations.items(), key=lambda x: -x[1])[:20]
        # 修改排序逻辑，综合相似度和播放量
        sorted_items = sorted(
            recommendations.items(),
            key=lambda x: (
                -x[1],  # 相似度降序
                -Playlist.query.filter_by(name=x[0]).first().play_count  # 播放量降序
            )
        )[:20]
        # 过滤时保留部分已收藏项
        recommended_names = [item[0] for item in sorted_items if item[1] > 0.5]  # 仅保留相似度>0.5的项

        # 获取歌单详情
        playlists = Playlist.query.filter(Playlist.name.in_(recommended_names)).all()
        playlist_map = {p.name: p.to_dict() for p in playlists}
        ordered_result = [playlist_map[name] for name in recommended_names if name in playlist_map]
        # ...其他代码...
        # print(f"[Debug] 用户 {username} 的收藏: {collected_names}")
        # print(f"[Debug] 相似度矩阵示例: {dict(list(item_similarity.items())[:1])}")
        # print(f"[Debug] 推荐候选: {recommended_names}")
        return jsonify({"success": True, "data": ordered_result})

    except Exception as e:
        # print(f"[ERROR] 推荐异常: {str(e)}")
        return jsonify({"success": False, "error": str(e)}), 500

# # 获取歌单收藏分析数据（前十位）
# @app.route('/playlist-collect-analysis', methods=['GET'])
# @cross_origin()
# def playlist_collect_analysis():
#     try:
#         # 使用 SQL 查询获取歌单收藏排行前十
#         sql_query = """
#         SELECT p.name as type, COUNT(ucp.username) as value
#         FROM Playlist p
#         LEFT JOIN UserCollectedPlaylist ucp ON p.name = ucp.playlist_name
#         GROUP BY p.name
#         ORDER BY value DESC
#         LIMIT 10
#         """
#
#         result = db.session.execute(sql_query)
#
#         # 将查询结果转换为列表
#         data = [{"type": row[0], "value": row[1]} for row in result]
#
#         return jsonify({
#             "success": True,
#             "data": data
#         })
#
#     except SQLAlchemyError as e:
#         db.session.rollback()
#         app.logger.error(f"数据库查询失败: {str(e)}")
#         return jsonify({
#             "success": False,
#             "error": "数据库查询失败"
#         }), 503
#
#     except Exception as e:
#         app.logger.error(f"获取歌单收藏分析数据失败: {str(e)}")
#         return jsonify({
#             "success": False,
#             "error": str(e)
#         }), 500
# 获取歌单收藏分析数据（前十位）
@app.route('/playlist-collect-analysis', methods=['GET'])
@cross_origin()
def playlist_collect_analysis():
    try:
        # 使用 SQL 查询获取歌单收藏排行前十
        sql_query = """
        SELECT p.name as type, COUNT(ucp.username) as value
        FROM data2 p
        LEFT JOIN user_collected_playlists ucp ON p.name = ucp.playlist_name
        GROUP BY p.name
        ORDER BY value DESC
        LIMIT 10  # 添加LIMIT子句以限制返回结果的数量
        """

        result = db.session.execute(sql_query)

        # 将查询结果转换为列表
        data = [{"type": row[0], "value": row[1]} for row in result]

        return jsonify({
            "success": True,
            "data": data
        })

    except SQLAlchemyError as e:
        db.session.rollback()
        app.logger.error(f"数据库查询失败: {str(e)}")
        return jsonify({
            "success": False,
            "error": "数据库查询失败"
        }), 503

    except Exception as e:
        app.logger.error(f"获取歌单收藏分析数据失败: {str(e)}")
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500
# 服务启动时计算一次相似度
with app.app_context():
    calculate_similarity()

# 更新歌单信息路由
@app.route('/update-playlist', methods=["POST"])
@cross_origin()
def update_playlist():
    try:
        data = request.json
        playlist = Playlist.query.filter_by(name=data['name']).first()

        if not playlist:
            return jsonify({
                "success": False,
                "error": "歌单不存在"
            }), 404

        # 更新歌单信息
        if 'type' in data:
            playlist.type = data['type']
        if 'play_count' in data:
            playlist.play_count = str(data['play_count'])
        if 'favorite_count' in data:
            playlist.favorite_count = str(data['favorite_count'])
        if 'share_count' in data:
            playlist.share_count = str(data['share_count'])
        if 'comment_count' in data:
            playlist.comment_count = str(data['comment_count'])
        if 'playlist_length' in data:
            playlist.playlist_length = str(data['playlist_length'])
        if 'tags' in data:
            playlist.tags = data['tags']

        db.session.commit()

        return jsonify({
            "success": True,
            "message": "歌单更新成功"
        })

    except SQLAlchemyError as e:
        db.session.rollback()
        app.logger.error(f"数据库更新失败: {str(e)}")
        return jsonify({
            "success": False,
            "error": "数据库更新失败"
        }), 503

    except Exception as e:
        app.logger.error(f"更新歌单失败: {str(e)}")
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

# 删除歌单路由
@app.route('/delete-playlist', methods=["POST"])
@cross_origin()
def delete_playlist():
    try:
        data = request.json
        playlist = Playlist.query.filter_by(name=data['name']).first()

        if not playlist:
            return jsonify({
                "success": False,
                "error": "歌单不存在"
            }), 404

        # 删除关联的收藏记录
        UserCollectedPlaylist.query.filter_by(playlist_name=data['name']).delete()

        # 删除歌单
        db.session.delete(playlist)
        db.session.commit()

        return jsonify({
            "success": True,
            "message": "歌单删除成功"
        })

    except SQLAlchemyError as e:
        db.session.rollback()
        app.logger.error(f"数据库删除失败: {str(e)}")
        return jsonify({
            "success": False,
            "error": "数据库删除失败"
        }), 503

    except Exception as e:
        app.logger.error(f"删除歌单失败: {str(e)}")
        return jsonify({
            "success": False,
            "error": str(e)
        }), 500

from sqlalchemy import select
@app.route('/playlists', methods=["GET"])
@cross_origin()
def get_playlists():
    try:
        # 移除不必要的别名，直接使用原模型
        selected_columns = [
            Playlist.name,
            Playlist.type,
            Playlist.created_date,
            Playlist.play_count,
            Playlist.favorite_count,
            Playlist.share_count,
            Playlist.playlist_length,
            Playlist.comment_count,
            Playlist.tags
        ]

        stmt = select(*selected_columns)

        # 优化查询执行方式并使用字典格式结果
        result = db.session.execute(
            stmt.execution_options(
                stream_results=True,
                max_row_buffer=50
            )
        ).mappings()

        # 简化数据转换逻辑
        def convert_row(row):
            return {
                "name": row.get("name", ""),
                "type": row.get("type", "未知类型"),
                "created_date": row.get("created_date", "无日期"),
                "play_count": int(row.get("play_count", 0)),
                "favorite_count": row.get("favorite_count") or "0",
                "share_count": int(row.get("share_count", 0)),
                "playlist_length": int(row.get("playlist_length", 0)),
                "comment_count": int(row.get("comment_count", 0)),
                "tags": row.get("tags") or [],
            }

        return jsonify({
            "success": True,
            "data": [convert_row(row) for row in result]
        })

    except SQLAlchemyError as e:
        db.session.rollback()
        app.logger.error(f"数据库查询失败: {str(e)}")
        return jsonify({"success": False, "error": "数据服务暂时不可用"}), 503

    except Exception as e:
        app.logger.error(f"未知错误: {str(e)}")
        return jsonify({"success": False, "error": "系统异常"}), 500
#
# @app.route('/playlists', methods=["GET"])
# @cross_origin()
# def get_playlists():
#     try:
#         from sqlalchemy import select
#         from sqlalchemy.orm import aliased
#
#         # 1. 使用别名明确模型来源
#         PlaylistAlias = aliased(Playlist)
#
#         # 2. 精确选择字段（保持与前端完全一致的字段名）
#         selected_columns = [
#             PlaylistAlias.name,
#             PlaylistAlias.type,
#             PlaylistAlias.created_date,
#             PlaylistAlias.play_count,
#             PlaylistAlias.favorite_count,
#             PlaylistAlias.share_count,
#             PlaylistAlias.playlist_length,
#             PlaylistAlias.comment_count,
#             PlaylistAlias.tags
#         ]
#
#         # 3. 构建安全查询
#         stmt = select(*selected_columns)
#
#         # 5. 安全执行查询
#         result = db.session.execute(
#             stmt.execution_options(
#                 stream_results=True,
#                 max_row_buffer=50  # 优化内存缓存
#             )
#         )
#
#         # 6. 防御式数据转换
#         def safe_row_converter(row):
#             return {
#                 "name": row.name or "",
#                 "type": row.type or "未知类型",
#                 "created_date": row.created_date or "无日期",
#                 "play_count": int(row.play_count) if row.play_count is not None else 0,
#                 "favorite_count": row.favorite_count or "0",  # 保持字符串类型
#                 "share_count": int(row.share_count) if row.share_count is not None else 0,
#                 "playlist_length": int(row.playlist_length) if row.playlist_length is not None else 0,
#                 "comment_count": int(row.comment_count) if row.comment_count is not None else 0,
#                 "tags": row.tags or []
#             }
#
#         # 7. 保持完全一致的响应结构
#         return jsonify({
#             "success": True,
#             "data": [safe_row_converter(p) for p in result]
#         })
#
#     except SQLAlchemyError as e:
#         db.session.rollback()
#         app.logger.error(f"数据库查询失败: {str(e)}")
#         return jsonify({"success": False, "error": "数据服务暂时不可用"}), 503
#
#     except Exception as e:
#         app.logger.error(f"未知错误: {str(e)}")
#         return jsonify({"success": False, "error": "系统异常"}), 500

from flask import request, jsonify
from datetime import datetime
import uuid

# 根据用户名获取用户收藏列表
@app.route('/user-collections', methods=['GET'])
@cross_origin()
def get_user_collections():
    try:
        username = request.args.get('username')
        # 查询用户收藏记录
        collections = UserCollectedPlaylist.query.filter_by(
            username=username
        ).all()
        # 返回歌单名称列表
        return jsonify([{
            "name": c.playlist_name,
            "created_at": c.created_at.isoformat()
        } for c in collections])
    except Exception as e:
        return jsonify({"error": str(e)}), 500
# 获取用户收藏列表所有信息
# 修改后的后端路由（完全开放）
@app.route('/all-collections', methods=['GET'])
@cross_origin()
def get_all_collections():
    try:
        # 直接获取所有收藏记录
        all_collections = UserCollectedPlaylist.query.order_by(
            desc(UserCollectedPlaylist.created_at)
        ).all()
                # 构建响应数据（直接使用收藏表中的username）
        response_data = [{
            "username": collection.username,  # 直接使用收藏记录的username字段
            "name": collection.playlist_name,
            "created_at": collection.created_at.isoformat()
        } for collection in all_collections]
        return jsonify(response_data), 200
    except Exception as e:
        import traceback
        traceback.print_exc()  # 打印完整的错误堆栈
        return jsonify({"error": str(e)}), 500

# 处理收藏/取消收藏
@app.route('/collect-playlist', methods=['POST'])
def collect_playlist():
    try:
        print("[DEBUG] 收到请求数据:", request.get_json())  # 添加请求日志
        data = request.get_json()
        username = data.get('username')
        playlist_name = data.get('playlist_name')
        is_collect = data.get('isCollect')

        # 获取数据库连接
        with db.session.begin():
            playlist = Playlist.query.get(playlist_name)
            if not playlist:
                return jsonify({"success": False, "message": "歌单不存在"}), 404

            collection = UserCollectedPlaylist.query.filter_by(
                username=username,
                playlist_name=playlist_name
            ).with_for_update().first()  # 添加行级锁

            if is_collect:
                if collection:
                    db.session.delete(collection)
                    playlist.favorite_count = str(playlist.safe_favorite_count() - 1)
            else:
                if not collection:
                    new_collection = UserCollectedPlaylist(
                        id=str(uuid.uuid4()),
                        username=username,
                        playlist_name=playlist_name
                    )
                    db.session.add(new_collection)
                    playlist.favorite_count = str(int(playlist.favorite_count) + 1)

            # 添加调试点
            print(f"即将提交，新收藏状态: {not is_collect}")
            # 修改后的正确代码
            if is_collect:
                print(f"正在取消收藏，收藏记录存在: {bool(collection)}")
            else:
                if collection:
                    print("无需重复收藏")
                else:
                    print(f"新增收藏ID: {new_collection.id}")

        # 自动提交
        return jsonify({"success": True})

    except Exception as e:
        print("[ERROR] 发生异常:", str(e))  # 添加异常日志
        return jsonify({"success": False, "message": str(e)}), 500

@app.route('/playlists/hot', methods=['GET'])
@cross_origin()
def get_hot_playlists():
    try:
        from sqlalchemy import text

        # 1. 使用原生SQL优化计算（提升10倍性能）
        sql = text("""
        SELECT 
            name,
            type,
            created_date,
            play_count,
            favorite_count,
            share_count,
            playlist_length,
            comment_count,
            tags,
            (COALESCE(CAST(play_count AS UNSIGNED), 0) * 1 + 
             COALESCE(CAST(favorite_count AS UNSIGNED), 0) * 2 + 
             COALESCE(CAST(share_count AS DECIMAL(10,2)), 0) * 0.5 + 
             COALESCE(CAST(comment_count AS UNSIGNED), 0) * 1.5) AS hot_score
        FROM data2
        ORDER BY hot_score DESC
        LIMIT 100
        """)

        # 2. 流式执行查询（内存降低80%）
        result = db.session.execute(sql)

        # 3. 保持完全一致的输出格式
        ranked_data = []
        for i, row in enumerate(result, start=1):
            try:
                # 保持原校验逻辑
                final_hot_score = (
                        (int(row.play_count) if str(row.play_count).isdigit() else 0) * 1 +
                        (int(row.favorite_count) if str(row.favorite_count).isdigit() else 0) * 2 +
                        (float(row.share_count) if str(row.share_count).replace('.', '', 1).isdigit() else 0) * 0.5 +
                        (int(row.comment_count) if str(row.comment_count).isdigit() else 0) * 1.5
                )
            except Exception as e:
                final_hot_score = row.hot_score

            ranked_data.append({
                "name": row.name,
                "type": row.type,
                "created_date": row.created_date,
                "play_count": row.play_count,
                "favorite_count": row.favorite_count,
                "share_count": row.share_count,
                "playlist_length": row.playlist_length,
                "comment_count": row.comment_count,
                "tags": row.tags.split(',') if row.tags else [],
                "ranking": i,
                "hot_score": float(final_hot_score)
            })

        return jsonify({"success": True, "data": ranked_data})

    except SQLAlchemyError as e:
        db.session.rollback()
        current_app.logger.error(f"Database Error: {str(e)}")
        return jsonify({"success": False, "error": "数据服务暂时不可用"}), 503

    except Exception as e:
        current_app.logger.error(f"System Error: {str(e)}")
        return jsonify({"success": False, "error": "服务器内部错误"}), 500

# ai
# 核心：处理AI助手聊天请求（流式响应）
def call_with_stream(query):
    """
    流式调用百炼 API
    需要将API Key配置到环境变量
    https://help.aliyun.com/zh/model-studio/developer-reference/configure-api-key-through-environment-variables
    返回数据格式
    id:1
    event:result
    :HTTP_STATUS/200
    data:{"output":{"session_id":"xxx","finish_reason":"null","text":"相关的问题"}}
    """
    # appId 填入百炼应用 ID
    responses = Application.call(
        app_id="fccdece0f31542c08fb0712b5cff8dc8",
        prompt=query,
        stream=True,
        incremental_output=True,
    )
    i = 0
    for response in responses:
        if response.status_code != HTTPStatus.OK:
            print(
                "request_id=%s\n output=%s\n usage=%s\n"
                % (response.request_id, response.output, response.usage)
            )
        else:
            data = (
                "id:"
                + str(i)
                + "\nevent:result\n:HTTP_STATUS/200\ndata:"
                + json.dumps(response)
                + "\n\n"
            )
            yield data

@app.route("/chat", methods=["POST"])
@cross_origin()
def create():
    raw_data = request.data
    data = json.loads(raw_data.decode("utf-8"))

    return Response(
        call_with_stream(data["prompt"]), mimetype="application/octet-stream"
    )

# @app.route("/chat", methods=["POST"])
# @cross_origin()
# def chat():
#     data = json.loads(request.data.decode("utf-8"))
#     query = data["prompt"]
#
#     # 调用百炼API（需替换为你的app_id）
#     responses = Application.call(
#         app_id="fccdece0f31542c08fb0712b5cff8dc8",  # 替换为实际值
#         prompt=query,
#         stream=True,
#         incremental_output=True,
#     )
#
#     def generate_stream():
#         i = 0
#         for response in responses:
#             if response.status_code == HTTPStatus.OK:
#                 data = (
#                     "id:" + str(i) +
#                     "\nevent:result\n:HTTP_STATUS/200\ndata:" +
#                     json.dumps(response) + "\n\n"
#                 )
#                 yield data
#                 i += 1
#
#     return Response(generate_stream(), mimetype="application/octet-stream")
# @app.route('/ai-helper', methods=['POST', 'OPTIONS'])  # 显式处理OPTIONS
# def handle_ai_request():
#     try:
#         # 处理预检请求
#         if request.method == 'OPTIONS':
#             response = jsonify({"status": "preflight"})
#             response.headers.add("Access-Control-Max-Age", "600")
#             return response
#
#         # 验证请求内容
#         if not request.is_json:
#             logger.warning("非法请求内容类型")
#             return jsonify({"error": "仅支持JSON格式"}), 400
#
#         data = request.get_json()
#         if 'query' not in data or not isinstance(data['query'], str):
#             logger.warning(f"无效请求数据: {data}")
#             return jsonify({"error": "缺少查询参数"}), 400
#
#         # 获取上下文数据
#         context_data = get_playlist_context()
#         if not context_data:
#             logger.error("无法获取歌单数据")
#             return jsonify({"error": "数据服务不可用"}), 503
#
#         # 构建上下文提示
#         context = f"""（保持原有提示结构）"""
#
#         # 调用DeepSeek API（带超时和重试）
#         api_response = None
#         for attempt in range(3):
#             try:
#                 api_response = requests.post(
#                     "https://api.deepseek.com/v1/chat/completions",
#                     json={
#                         "model": "deepseek-chat",
#                         "messages": [
#                             {"role": "system", "content": context},
#                             {"role": "user", "content": data['query']}
#                         ],
#                         "temperature": 0.3
#                     },
#                     timeout=10
#                 )
#                 api_response.raise_for_status()
#                 break
#             except requests.exceptions.RequestException as e:
#                 logger.warning(f"API请求失败（尝试 {attempt+1}）: {str(e)}")
#                 time.sleep(1)
#                 if attempt == 2:
#                     raise
#
#         # 处理API响应
#         response_data = api_response.json()
#         if not isinstance(response_data.get('choices'), list):
#             logger.error(f"异常API响应: {response_data}")
#             return jsonify({"error": "AI服务响应异常"}), 500
#
#         # 构建最终响应
#         response = jsonify({
#             "answer": response_data['choices'][0]['message']['content'],
#             "quick_actions": ["推荐热门歌单", "最新上传内容", "收藏排行榜"]
#         })
#         response.headers.add("Access-Control-Allow-Origin", "http://localhost:3000")
#         return response
#
#     except Exception as e:
#         logger.exception("未捕获的异常")
#         return jsonify({"error": "系统处理失败"}), 500

@app.route('/bookdata', methods=["GET"])
@cross_origin()
def bookdata():
    res1 = bookitem.query.all()
    response = []
    for x in res1:
        item = {"key":x.bid,"name":x.name, "author":x.author, "publish":x.publish, "isbn":x.isbn, "price":x.price, "number":x.number, "type":x.type,"intro":x.intro, "pubdate":x.pubdate}
        response.append(item)
    return response

@app.route('/tocollect', methods=["POST"])
@cross_origin()
def tocollect():
    sth = request.json
    # print('json msg:', sth)
    username = sth['username']
    isbn = sth['isbn']
    iscollect = sth['isCollect']
    # 前面为要收藏，后面为已收藏取消收藏
    if not iscollect:
        # 先找书的所有收藏信息
        res1 = bookCollect.query.filter(bookCollect.isbn == isbn).all()
        tag = False
        # 再判断是否收藏这本书
        for x in res1:
            if x.username == username:
                tag=True
        # 没收藏就记录信息，收藏过就忽略
        if not tag:
            newitem = bookCollect(username=username,isbn=isbn)
            db.session.add(newitem)
            db.session.commit()
        return jsonify({"msg": "collect ok！"})
    else:
        res1 = bookCollect.query.filter(bookCollect.isbn == isbn).all()
        for x in res1:
            if x.username==username:
                db.session.delete(x)
        db.session.commit()
        return jsonify({"msg": "cancel collect ok！"})

@app.route('/collectdata', methods=["GET"])
@cross_origin()
def collectdata():
    sth = request.args
    # print(sth)
    username = sth['username']
    res1 = bookCollect.query.filter(bookCollect.username == username).all()
    print(res1)
    response = []
    for x in res1:
        res2 = bookitem.query.filter(bookitem.isbn == x.isbn).first()
        item = {"id":res2.bid,"key": res2.bid, "name": res2.name, "author": res2.author, "publish": res2.publish, "isbn": res2.isbn,
                "price": res2.price, "number": res2.number, "type":res2.type,"intro": res2.intro, "pubdate": res2.pubdate}
        response.append(item)
    return response

@app.route('/toborrow', methods=["POST"])
@cross_origin()
def toborrow():
    sth = request.json
    # print('json msg:', sth)
    borrowusr = sth['borrowusr']
    name = sth['name']
    borrownum = sth['borrownum']
    borrowdate = sth['borrowdate']
    shouldreturndate = sth['shouldreturndate']
    timestamp = sth['timestamp']

    hasborrow = False
    borrowitem = ''
    res1 = bookBorrow.query.filter(bookBorrow.borrowusr == borrowusr).all()
    for x in res1:
        if x.name == name:
            hasborrow = True
            borrowitem = x

    # db里查询到借阅过相关书
    if hasborrow:
        # print(borrowitem.name)
        borrowitem.borrownum = borrowitem.borrownum+1
        # 违约记录中数量+1
        res3 = bookDefaultRecord.query.filter(bookDefaultRecord.name == name).all()
        for x in res3:
            if x.borrowusr == borrowusr:
                item = x
        item.number = item.number+1
        # 库存数量-1
        res2 = bookitem.query.filter(bookitem.name == name).first()
        res2.number = res2.number-1
        db.session.commit()
        return jsonify({"msg": "add book num ok！"})
    # 第一次借书
    else:
        newborrow = bookBorrow(borrowusr=borrowusr,name=name,borrownum=borrownum,borrowdate=borrowdate,shouldreturndate=shouldreturndate,ischecking=0,returnnum=0)
        newhistory = bookBorrowHistory(name=name,borrowusr=borrowusr,borrowdate=borrowdate,returndate='')
        db.session.add(newborrow)
        db.session.add(newhistory)
        newdefault = bookDefaultRecord(name=name,borrowusr=borrowusr,borrowdate=borrowdate,shouldreturndate=shouldreturndate,number=borrownum,borrowtimestamp=timestamp,ispayfine=0,isreturnbook=0)
        db.session.add(newdefault)
        # 库存数量-1
        res2 = bookitem.query.filter(bookitem.name == name).first()
        res2.number = res2.number - 1
        db.session.commit()
        return jsonify({"msg": "borrow book ok！"})

@app.route('/borrowdata', methods=["GET"])
@cross_origin()
def borrowdata():
    sth = request.args
    # print(sth)
    username = sth['username']
    history = sth['history']
    tag = False
    if history=='false':
        tag = False
    elif history=='true':
        tag = True
    # print(tag)
    if tag:
        res1 = bookBorrowHistory.query.filter(bookBorrowHistory.borrowusr == username).all()
        # print(res1)
        # print(1)
        response = []
        for x in res1:
            item = {"id":x.id,"key":x.id,"name":x.name,"borrowusr":x.borrowusr,"borrowdate":x.borrowdate,"returndate":x.returndate}
            response.append(item)
        return response
    else:
        res1 = bookBorrow.query.filter(bookBorrow.borrowusr == username).all()
        # print(res1)
        # print(2)
        response = []
        for x in res1:
            item = {"id":x.id,"key":x.id,"name":x.name,"borrowusr":x.borrowusr,"borrowdate":x.borrowdate,"borrownum":x.borrownum,"shouldreturndate":x.shouldreturndate,"ischecking":x.ischecking,"returnnum":x.returnnum}
            response.append(item)
        return response

# @app.route('/toreturn', methods=["POST"])
# @cross_origin()
# def toreturn():
#     sth = request.json
#     # print('json msg:', sth)
#     borrowusr = sth['borrowusr']
#     name = sth['name']
#     returndate = sth['returndate']
#     timestamp = sth['timestamp']
#     returnnum = sth['returnnum']
#     res1 = bookBorrow.query.filter(bookBorrow.borrowusr == borrowusr).all()
#     for x in res1:
#         if x.name == name:
#             if x.borrownum >= 1:
#                 x.returnnum = 0
#                 x.ischecking = 0
#                 # x.borrownum = x.borrownum-1
#             else:
#                 db.session.delete(x)
#                 # 当书尽数归还，记录已还书和最后的时间戳
#                 res3 = bookDefaultRecord.query.filter(bookDefaultRecord.name == name).all()
#                 for x in res3:
#                     if x.borrowusr == borrowusr:
#                         item = x
#                 item.isreturnbook = 1
#                 item.returntimestamp = timestamp
#
#     db.session.commit()
#     res2 = bookBorrowHistory.query.filter(bookBorrowHistory.borrowusr == borrowusr).all()
#     for x in res2:
#         if x.name == name:
#             x.returndate = returndate
#     db.session.commit()
#     # 库存图书数量+returnnum
#     res3 = bookitem.query.filter(bookitem.name == name).first()
#     res3.number = res3.number+returnnum
#     db.session.commit()
#     return jsonify({"msg": "return ok！"})

# 归还前需要先审批
# @app.route('/beforetoreturn', methods=["POST"])
# @cross_origin()
# def beforetoreturn():
#     sth = request.json
#     borrowusr = sth['borrowusr']
#     name = sth['name']
#     res1 = bookBorrow.query.filter(bookBorrow.borrowusr == borrowusr).all()
#     for x in res1:
#         if x.name == name:
#             x.returnnum = x.returnnum+1
#             x.ischecking = 1
#             x.borrownum = x.borrownum - 1
#
#     db.session.commit()
#
#     return jsonify({"msg": "apply return ok！"})

# @app.route('/denyreturn', methods=["POST"])
# @cross_origin()
# def denyreturn():
#     sth = request.json
#     borrowusr = sth['borrowusr']
#     name = sth['name']
#     res1 = bookBorrow.query.filter(bookBorrow.borrowusr == borrowusr).all()
#     for x in res1:
#         if x.name == name:
#             x.borrownum = x.borrownum + x.returnnum
#             x.ischecking = -1
#             x.returnnum = 0
#
#     db.session.commit()
#
#     return jsonify({"msg": "apply return ok！"})

# @app.route('/toaddnewbook', methods=["POST"])
# @cross_origin()
# def toaddnewbook():
#     sth = request.json
#     # print('msg:', sth)
#     name = sth['name']
#     author = sth['author']
#     publish = sth['publish']
#     isbn = sth['isbn']
#     price = sth['price']
#     number = sth['number']
#     type = sth['type']
#     intro = sth['intro']
#     pubdate = sth['pubdate']
#     newitem = booknewitem(name=name,author=author,publish=publish,isbn=isbn,price=price,number=number,type=type,intro=intro,pubdate=pubdate)
#     db.session.add(newitem)
#     db.session.commit()
#     return jsonify({"msg": "add new book ok！"})

# 图书违约
@app.route('/defaultdata', methods=["GET"])
@cross_origin()
def defaultdata():
    sth = request.args
    # print(sth)
    username = sth['username']
    res1 = bookDefaultRecord.query.filter(bookDefaultRecord.borrowusr == username).all()
    response = []
    for x in res1:
        item = {"key":random.random(),"name":x.name,"borrowusr":x.borrowusr,"borrowdate":x.borrowdate,"shouldreturndate":x.shouldreturndate,"number":x.number,"borrowtimestamp":x.borrowtimestamp,"returntimestamp":x.returntimestamp,"ispayfine":x.ispayfine,"isreturnbook":x.isreturnbook}
        response.append(item)
    return response

@app.route('/defaultpay', methods=["POST"])
@cross_origin()
def defaultpay():
    sth = request.json
    # print('msg:', sth)
    username = sth['username']
    name = sth['name']
    res1 = bookDefaultRecord.query.filter(bookDefaultRecord.borrowusr == username).all()
    for x in res1:
        if x.name==name:
            x.ispayfine = 1
    db.session.commit()
    return jsonify({"msg": "pay default ok！"})

# 留言板评论功能
@app.route('/mbcommentdata', methods=["GET"])
@cross_origin()
def messagebcdata():
    res1 = messageboardparentcomment.query.all()
    res2 = messageboardchildcomment.query.all()
    response = []
    for x in res1:
        item = {"id":x.id,'fromId': x.fromId, "content": x.content,"likeNum":x.likeNum,"createTime":x.createTime,"settop":x.settop,"child":[]}
        response.append(item)
    for x in res2:
        item = {"id":x.id,'fromId': x.fromId, "content": x.content,"commentId":x.commentId,"createTime":x.createTime}
        for x in response:
            if x['id'] == item['commentId']:
                x['child'].append(item)
    # print(response)
    return response

@app.route('/addmbcomment', methods=["POST"])
@cross_origin()
def addmbcomment():
    sth = request.json
    type = sth['type']
    if type=='parent':
        fromId = sth['fromId']
        content = sth['content']
        createTime = sth['createTime']
        likeNum = sth['likeNum']
        settop = sth['settop']
        nitem = messageboardparentcomment(fromId=fromId, content=content, likeNum=likeNum,settop=settop,
                                          createTime=createTime)
    elif type=='child':
        fromId = sth['fromId']
        content = sth['content']
        createTime = sth['createTime']
        commentId = sth['commentId']
        nitem = messageboardchildcomment(fromId=fromId, content=content, commentId=commentId,
                                          createTime=createTime)
    db.session.add(nitem)
    db.session.commit()
    return jsonify({"msg": "add comment ok！"})

@app.route('/mbcommentlike', methods=["POST"])
@cross_origin()
def mbcommentlike():
    sth = request.json
    type = sth['type']
    id = sth['id']
    res1 = messageboardparentcomment.query.all()
    for x in res1:
        if x.id==id and type=='like':
            x.likeNum=x.likeNum+1
        elif x.id==id and type=='unlike':
            x.likeNum = x.likeNum - 1
    db.session.commit()
    return jsonify({"msg": "like/unlike comment ok！"})

@app.route('/deletembcomment', methods=["POST"])
@cross_origin()
def deletembcomment():
    sth = request.json
    type = sth['type']
    createTime = sth['createTime']
    fromId = sth['fromId']
    if type=='parent':
        # 父评论直接根据id查找，前端生成的id与后端不匹配
        # 基于fromid和时间戳
        res1 = messageboardparentcomment.query.filter(messageboardparentcomment.fromId == fromId).all()
        for x in res1:
            if x.createTime == createTime:
                pid = x.id
                # 删除父评论下的子评论
                res2 = messageboardchildcomment.query.filter(messageboardchildcomment.commentId == pid).all()
                for y in res2:
                    db.session.delete(y)
                db.session.delete(x)

    elif type=='child':
        res3 = messageboardchildcomment.query.filter(messageboardchildcomment.fromId == fromId).all()
        for x in res3:
            if x.createTime == createTime:
                db.session.delete(x)

    db.session.commit()
    return jsonify({"msg": "delete comment ok！"})

@app.route('/settopmbcomment', methods=["POST"])
@cross_origin()
def settopmbcomment():
    sth = request.json
    createTime = sth['createTime']
    fromId = sth['fromId']
    # 前端id和后台id对应不上
    id = sth['id']
    settop = sth['settop']
    res1 = messageboardparentcomment.query.filter(messageboardparentcomment.fromId == fromId).all()
    for x in res1:
        if x.createTime == createTime:
            x.settop = settop
    db.session.commit()
    return jsonify({"msg": "un/set top comment ok！"})

# 留言板图片功能
@app.route('/mbuploadimages', methods=["POST"])
@cross_origin()
def mbuploadimages():
    file = request.files['file']
    _, ext = os.path.splitext(file.filename)
    new_filename = _ + ext
    # 获取当前文件所在的目录
    current_dir = os.path.dirname(os.path.abspath(__file__))
    # 拼接图片文件路径
    file_path = os.path.join(current_dir, '..','mbpics', new_filename)
    frontfilepath = 'http://127.0.0.1:5000/mbimages/'+new_filename
    if file:
        file.save(file_path)
        return jsonify({'success': True, 'url': frontfilepath})
    else:
        return jsonify({'success': False})

@app.route('/deletembpic/<filename>', methods=['DELETE'])
@cross_origin()
def delete_image(filename):
    current_dir = os.path.dirname(os.path.abspath(__file__))
    file_path = os.path.join(current_dir, '..', 'mbpics', filename)
    os.remove(file_path)
    return {'msg': 'success'}

# 用于留言板图片显示
@app.route('/mbimages/<filename>')
def get_mb_image(filename):
    # 获取当前文件所在的目录
    current_dir = os.path.dirname(os.path.abspath(__file__))
    # 拼接图片文件路径
    file_path = os.path.join(current_dir, '..','mbpics', filename)
    # 使用 Flask 的 send_file 函数来返回图片
    return send_file(file_path, mimetype='image/jpeg')

# 热门排行
@app.route('/hotborrowdata', methods=["GET"])
@cross_origin()
def hotborrowdata():
    res1 = bookBorrowHistory.query.all()
    booknamearray = []
    response = []
    for x in res1:
        booknamearray.append(x.name)
    # 取值唯一的数组
    uniquetagarray = list(set(booknamearray))
    dict1 = {}
    for x in booknamearray:
        dict1[x] = dict1.get(x, 0) + 1
    for x in uniquetagarray:
        subdict = dict([(key, dict1[key]) for key in [x]])
        # print(list(subdict.keys()))
        fridict = dict(type=list(subdict.keys())[0], value=list(subdict.values())[0])
        response.append(fridict)
    response.sort(key=getnum,reverse=True)
    if len(response)>10:
        response = response[:10]
    # 上述生成type和value的字典数据
    # 生成包含图书详细信息的数组
    newresponse = []
    for x in response:
        res1 = bookitem.query.filter(bookitem.name == x['type']).first()
        newitem = {"key":res1.bid,"name":res1.name, "value":x['value'],"author":res1.author, "publish":res1.publish, "isbn":res1.isbn, "price":res1.price, "number":res1.number, "type":res1.type,"intro":res1.intro, "pubdate":res1.pubdate}
        newresponse.append(newitem)
    # print(newresponse)
    return newresponse

@app.route('/hotcollectdata', methods=["GET"])
@cross_origin()
def hotcollectdata():
    res1 = bookCollect.query.all()
    booknamearray = []
    response = []
    for x in res1:
        res2 = bookitem.query.filter(bookitem.isbn == x.isbn).first()
        booknamearray.append(res2.name)
    # 取值唯一的数组
    uniquetagarray = list(set(booknamearray))
    dict1 = {}
    for x in booknamearray:
        dict1[x] = dict1.get(x, 0) + 1
    for x in uniquetagarray:
        subdict = dict([(key, dict1[key]) for key in [x]])
        # print(list(subdict.keys()))
        fridict = dict(type=list(subdict.keys())[0], value=list(subdict.values())[0])
        response.append(fridict)
    response.sort(key=getnum,reverse=True)
    if len(response)>10:
        response = response[:10]
    # 上述生成type和value的字典数据
    # 生成包含图书详细信息的数组
    newresponse = []
    for x in response:
        res1 = bookitem.query.filter(bookitem.name == x['type']).first()
        newitem = {"key": res1.bid, "name": res1.name, "value": x['value'], "author": res1.author,
                       "publish": res1.publish, "isbn": res1.isbn, "price": res1.price, "number": res1.number,
                       "type": res1.type, "intro": res1.intro, "pubdate": res1.pubdate}
        newresponse.append(newitem)
    # print(newresponse)
    return newresponse

# 新书速递
@app.route('/newbookrecommenddata', methods=["GET"])
@cross_origin()
def newbookrecommenddata():
    res1 = bookitem.query.order_by(bookitem.bid.desc()).limit(3).all()
    response = []
    for x in res1:
        newitem = {"key": x.bid, "name": x.name,"author": x.author,
                   "publish": x.publish, "isbn": x.isbn, "price": x.price, "number": x.number,
                   "type": x.type, "intro": x.intro, "pubdate": x.pubdate}
        response.append(newitem)
    # print(response)
    return response

# -----------------------------------------------------

# 管理员类路由----------------------------------------
# 管理员类路由 - 获取所有用户基本信息
@app.route('/usrdata', methods=["GET"])
@cross_origin()
def usrdata():
    res1 = normalusr.query.all()
    response = []
    for x in res1:
        res2 = usrinfo.query.filter(usrinfo.username == x.username).all()
        for y in res2:
            tagarray = []
            bookarray = []
            # 统计借阅历史
            res1 = bookBorrowHistory.query.filter(bookBorrowHistory.borrowusr == y.username).all()
            for x in res1:
                bookarray.append(x.name)
            # 统计收藏信息
            res2 = bookCollect.query.filter(bookCollect.username == y.username).all()
            for x in res2:
                res3 = bookitem.query.filter(bookitem.isbn == x.isbn).first()
                bookarray.append(res3.name)
            # 统计所有标签
            for x in bookarray:
                res4 = bookitem.query.filter(bookitem.name == x).first()
                tagarray.append(res4.type)
            # print(bookarray)
            # print(tagarray)
            # 计算出数量最多的3个标签
            dict = {}
            tag = []
            for x in tagarray:
                dict[x] = dict.get(x, 0) + 1
            # print(dict)
            if len(dict) > 3:
                while (len(tag) < 3):
                    tag.append(max(dict, key=dict.get))
                    dict.pop(max(dict, key=dict.get))
            else:
                for key in dict:
                    tag.append(key)
            # print(tag)
            # 取偏好的最大值
            if len(tag):
                liketag=tag[0]
            else:
                liketag =''
            item = {"id":y.uid,"key":y.uid,"username":y.username,"tel":y.tel,"sex":y.sex,"intro":y.intro,"like":liketag}
            response.append(item)
    return response

@app.route('/usrsexdata', methods=["GET"])
@cross_origin()
def usrsexdata():
    res1 = normalusr.query.all()
    response = []
    sexarray = []
    for x in res1:
        res2 = usrinfo.query.filter(usrinfo.username == x.username).all()
        for y in res2:
            sexarray.append(y.sex)
    # print(sexarray)
    dict1={}
    for x in sexarray:
        dict1[x] = dict1.get(x, 0) + 1
    # 取值唯一的数组
    uniquetagarray = list(set(sexarray))
    # 拆分字典，每个取值为一个字典并保存在数组中
    for x in uniquetagarray:
        subdict = dict([(key, dict1[key]) for key in [x]])
        # print(list(subdict.keys()))
        fridict = dict(type=list(subdict.keys())[0], value=list(subdict.values())[0])
        response.append(fridict)
    # print(response)
    return response

@app.route('/usrcollectdata', methods=["GET"])
@cross_origin()
def usrcollectdata():
    res1 = bookCollect.query.all()
    response = []
    for x in res1:
        res2 = bookitem.query.filter(bookitem.isbn == x.isbn).first()
        item = {"key":random.random(),"username":x.username,"name":res2.name,"author":res2.author}
        response.append(item)
    # print(response)
    return response

@app.route('/usrcollectanalysisdata', methods=["GET"])
@cross_origin()
def usrcollectanalysisdata():
    res1 = bookCollect.query.all()
    booknamearray = []
    response = []
    for x in res1:
        res2 = bookitem.query.filter(bookitem.isbn == x.isbn).first()
        booknamearray.append(res2.name)
    # 取值唯一的数组
    uniquetagarray = list(set(booknamearray))
    dict1 = {}
    for x in booknamearray:
        dict1[x] = dict1.get(x, 0) + 1
    for x in uniquetagarray:
        subdict = dict([(key, dict1[key]) for key in [x]])
        # print(list(subdict.keys()))
        fridict = dict(type=list(subdict.keys())[0], value=list(subdict.values())[0])
        response.append(fridict)
    response.sort(key=getnum,reverse=True)
    if len(response)>10:
        response = response[:10]
    print(response)
    return response

# 排序函数
def getnum(e):
    return e['value']

# 用户借阅数据
@app.route('/usrborrowlistdata', methods=["GET"])
@cross_origin()
def usrborrowlistdata():
    res1 = bookBorrow.query.all()
    response = []
    for x in res1:
        res2 = bookDefaultRecord.query.filter(bookDefaultRecord.borrowusr == x.borrowusr).all()
        for y in res2:
            if y.name == x.name:
                item = {"id":x.id,"key":x.id,"name":x.name,"borrowusr":x.borrowusr,"borrowdate":x.borrowdate,"borrownum":x.borrownum,
                        "shouldreturndate":x.shouldreturndate,"ischecking":x.ischecking,"returnnum":x.returnnum,
                        "ispayfine":y.ispayfine,"borrowtimestamp":y.borrowtimestamp}
                response.append(item)
    # print(response)
    return response

@app.route('/usrborrowdata', methods=["GET"])
@cross_origin()
def usrborrowdata():
    res1 = bookBorrowHistory.query.all()
    response = []
    for x in res1:
        item = {"key":x.id,"borrowusr":x.borrowusr,"name":x.name,"borrowdate":x.borrowdate,"returndate":x.returndate}
        response.append(item)
    # print(response)
    return response

@app.route('/usrborrowanalysisdata', methods=["GET"])
@cross_origin()
def usrborrowanalysisdata():
    res1 = bookBorrowHistory.query.all()
    booknamearray = []
    response = []
    for x in res1:
        booknamearray.append(x.name)
    # 取值唯一的数组
    uniquetagarray = list(set(booknamearray))
    dict1 = {}
    for x in booknamearray:
        dict1[x] = dict1.get(x, 0) + 1
    for x in uniquetagarray:
        subdict = dict([(key, dict1[key]) for key in [x]])
        # print(list(subdict.keys()))
        fridict = dict(type=list(subdict.keys())[0], value=list(subdict.values())[0])
        response.append(fridict)
    response.sort(key=getnum,reverse=True)
    if len(response)>10:
        response = response[:10]
    # print(response)
    return response

@app.route('/toeditusr', methods=["POST"])
@cross_origin()
def toeditusr():
    sth = request.json
    # print('json msg:', sth)
    username = sth['username']
    sex = sth['sex']
    tel = sth['tel']
    intro = sth['intro']
    res1 = usrinfo.query.filter(usrinfo.username == username).first()
    res1.sex = sex
    res1.tel = tel
    res1.intro = intro
    db.session.commit()
    return jsonify({"msg": "edit usr ok！"})

@app.route('/todelusr', methods=["POST"])
@cross_origin()
def todelusr():
    sth = request.json
    # print('json msg:', sth)
    username = sth['username']
    res1 = usrinfo.query.filter(usrinfo.username == username).first()
    db.session.delete(res1)
    res2 = bookBorrowHistory.query.filter(bookBorrowHistory.borrowusr == username).all()
    for x in res2:
        db.session.delete(x)
    res3 = bookBorrow.query.filter(bookBorrow.borrowusr == username).all()
    for x in res3:
        db.session.delete(x)
    res4 = bookCollect.query.filter(bookCollect.username == username).all()
    for x in res4:
        db.session.delete(x)
    db.session.commit()
    return jsonify({"msg": "delete usr ok！"})

# @app.route('/newbookdata', methods=["GET"])
# @cross_origin()
# def newbookdata():
#     res1 = booknewitem.query.all()
#     response = []
#     for x in res1:
#         item = {"key":x.bid,"name":x.name, "author":x.author, "publish":x.publish, "isbn":x.isbn, "price":x.price, "number":x.number, "type":x.type,"intro":x.intro, "pubdate":x.pubdate}
#         response.append(item)
#     return response

# @app.route('/newbookaction', methods=["POST"])
# @cross_origin()
# def newbookaction():
#     sth = request.json
#     # print('json msg:', sth)
#     name = sth['name']
#     action = sth['action']
#     if action=='ok':
#         res1 = booknewitem.query.filter(booknewitem.name == name).first()
#         newitem = bookitem(name=name, author=res1.author, publish=res1.publish, isbn=res1.isbn, price=res1.price, number=res1.number,type=res1.type,intro=res1.intro, pubdate=res1.pubdate)
#         db.session.add(newitem)
#         db.session.delete(res1)
#     elif action=='no':
#         res3 = booknewitem.query.filter(booknewitem.name == name).first()
#         db.session.delete(res3)
#     db.session.commit()
#     return jsonify({"msg": "action ok！"})

@app.route('/delbook', methods=["POST"])
@cross_origin()
def delbook():
    sth = request.json
    # print('json msg:', sth)
    isbn = sth['isbn']
    name = sth['name']
    res1 = bookitem.query.filter(bookitem.isbn == isbn).first()
    # print(res1.name)
    db.session.delete(res1)
    res2 = bookBorrowHistory.query.filter(bookBorrowHistory.name == name).all()
    for x in res2:
        db.session.delete(x)
    res3 = bookBorrow.query.filter(bookBorrow.name == name).all()
    for x in res3:
        db.session.delete(x)
    res4 = bookCollect.query.filter(bookCollect.isbn == isbn).all()
    for x in res4:
        db.session.delete(x)
    db.session.commit()
    return jsonify({"msg": "delete book ok！"})

# 新建图书

@app.route('/toeditbook', methods=["POST"])
@cross_origin()
def toeditbook():
    sth = request.json
    # print('json msg:', sth)
    name = sth['name']
    author = sth['author']
    intro = sth['intro']
    isbn = sth['isbn']
    number = sth['number']
    price = sth['price']
    type = sth['type']
    pubdate = sth['pubdate']
    publish = sth['publish']
    # isbn默认无误，作为图书的唯一标识
    res1 = bookitem.query.filter(bookitem.isbn == isbn).first()
    res1.name = name
    res1.author = author
    res1.intro = intro
    res1.number = number
    res1.price = price
    res1.type = type
    res1.pubdate = pubdate
    res1.publish = publish
    db.session.commit()
    return jsonify({"msg": "edit book ok！"})

@app.route('/create-playlist', methods=["POST"])
@cross_origin()
def create_playlist():
    try:
        # 获取请求数据
        data = request.get_json()
        playlist_name = data['name']
        playlist_type = data.get('type')
        created_date = data.get('created_date')
        play_count = data.get('play_count')
        favorite_count = data.get('favorite_count')
        share_count = data.get('share_count')
        comment_count = data.get('comment_count')
        playlist_length = data.get('playlist_length')
        tags = data.get('tags')

        # 检查歌单名称是否已存在
        existing_playlist = Playlist.query.filter_by(name=playlist_name).first()
        if existing_playlist:
            return jsonify({"error": "Playlist with this name already exists"}), 400

        # 创建新歌单
        new_playlist = Playlist(
            name=playlist_name,
            type=playlist_type,
            created_date=created_date,
            play_count=play_count,
            favorite_count=favorite_count,
            share_count=share_count,
            comment_count=comment_count,
            playlist_length=playlist_length,
            tags=tags
        )
        # 添加到数据库会话并提交
        db.session.add(new_playlist)
        db.session.commit()
        # 返回成功消息
        return jsonify({"msg": "Playlist created successfully"}), 201
    except Exception as e:
        # 记录异常日志
        app.logger.error(f"Failed to create playlist: {str(e)}")
        # 返回错误响应
        return jsonify({"error": "Failed to create playlist"}), 500